﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MYSQL_db.Model;
using System.Data;
using MySql.Data.MySqlClient;

namespace MYSQL_db.DAL
{
    class CustomerDAL
    {
        //把公共的代码封装到一个方法中，这样可以避免重复性的代码，提高代码复用性
        private static Customer ToCustomer(DataRow row)
        {   //把从数据库中查询到的行中字段值，封装到对象中
            Customer cust= new Customer();
            cust.Id = (long)row["Id"];
            cust.MemCardID = (string)row["MemCardID"];
            cust.Name = (string)SqlHelper.FromDbValue(row["Name"]);
            cust.Tel = (string)SqlHelper.FromDbValue(row["Tel"]);
            cust.Points = (int)SqlHelper.FromDbValue(row["points"]);
            cust.IdentityCard = (string)SqlHelper.FromDbValue(row["IdentityCard"]);
            return cust;
        }

        //通过MemCardID查询返回Customer对象
        public static Customer GetById(string MemCardID)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Customer where MemCardID=@MemCardID",
                new MySqlParameter("@MemCardID", MemCardID));
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count > 1)
            {
                throw new Exception("严重错误，查出多条数据！");
            }
            else
            {
                DataRow row = dt.Rows[0]; //获取返回表中的第一行数据
                return ToCustomer(row);    //将返回行中数据封装到Customer对象中
            }
        }
        //通过卡号删除操作者
        public static int DeleteById(string MemCardID)
        {
            return
            SqlHelper.ExecuteNonQuery("delete from T_Customer where MemCardID=@MemCardID",
                new MySqlParameter("@MemCardID", MemCardID));
        }
        //Customer对象作为参数，函数中取出属性值插入数据库
        public static int Insert(Customer cust)
        {
            return
            SqlHelper.ExecuteNonQuery(@"INSERT INTO T_Customer
                       (MemCardID,
                       Name,
                       Tel,
                       IdentityCard
                        )
                 Values(@MemCardID, @Name,@Tel,@IdentityCard)", new MySqlParameter("@MemCardID", cust.MemCardID),
                                               new MySqlParameter("@Name", SqlHelper.ToDbValue(cust.Name)),
                                               new MySqlParameter("@Tel",  SqlHelper.ToDbValue(cust.Tel)),
                                               new MySqlParameter("@IdentityCard",  SqlHelper.ToDbValue(cust.IdentityCard))
                                               );
        }

        //传入一个Operator对象，提取对象属性，修改数据库值
        public static int Update(Customer cust)
        {
            return
            SqlHelper.ExecuteNonQuery(@"UPDATE T_Customer
                       SET MemCardID = @MemCardID
                          ,Name = @Name
                          ,Tel]= @Tel
                          ,IdentityCard = @IdentityCard
                            WHERE MemCardID=@MemCardID",
                                  new MySqlParameter("@MemCardID", cust.MemCardID),
                                  new MySqlParameter("@Name", cust.Name),
                                  new MySqlParameter("@Tel", cust.Tel),
                                  new MySqlParameter("@IdentityCard", cust.IdentityCard)
                               );
        }
        //无参数查询数据库，返回Customer对象数组
        public static Customer[] GetAll()
        {

            DataTable table = SqlHelper.ExecuteDataTable("select * from T_Customer");
            Customer[] cust = new Customer[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i]; //循环取出表中每一行

                cust[i] = ToCustomer(row); //把取到的每一行加入对象数组
            }
            return cust; //返回对象数组
        }


        //接收table表，返回consume对象数组
        public static Customer[] Import(DataTable table)
        {
            Customer[] cust = new Customer[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                cust[i] = ToCustomer(table.Rows[i]);
            }
            return cust;
        }


    }
}
